package com.baizhi.dao;

import com.baizhi.entity.Title;
import com.baizhi.util.JDBCUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class TitleDAOImpl implements TitleDAO {
    @Override
    public List<Title> queryAll() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtil.getConnection();
            String sql = "select id,title,text,date,keyWord,likeCount,name from title";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            List<Title> list = new ArrayList<>();
            while (rs.next()) {
                Title t = new Title();
                t.setId(rs.getInt(1));
                t.setTitle(rs.getString(2));
                t.setText(rs.getString(3));
                t.setDate(rs.getString(4));
                t.setKeyWord(rs.getString(5));
                t.setLikeCount(rs.getInt(6));
                t.setName(rs.getString(7));
                list.add(t);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                rs = null;
                if (pstmt != null) {
                    pstmt.close();
                }
                pstmt = null;
                if (conn != null) {
                    conn.close();
                }
                conn = null;
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

    }

    @Override
    public void addTitle(Title title) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = JDBCUtil.getConnection();
            String sql = "insert into title (id,title,text,date,keyWord,likeCount,name)values (?,?,?,?,?,?,?)";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, title.getId());
            pstmt.setString(2, title.getTitle());
            pstmt.setString(3, title.getText());
            pstmt.setString(4, title.getDate());
            pstmt.setString(5, title.getKeyWord());
            pstmt.setInt(6, title.getLikeCount());
            pstmt.setString(7, title.getName());
            pstmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                pstmt = null;
                if (conn != null) {
                    conn.close();
                }
                conn = null;
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public Title showMyArticle(Title title) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtil.getConnection();
            String sql = "select id,title,text,date,keyWord,likeCount,name from title where name=?;";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, title.getName());
            rs = pstmt.executeQuery();
            Title t = null;
            while (rs.next()){
                t = new Title();
                t.setId(rs.getInt(1));
                t.setText(rs.getString(2));
                t.setTitle(rs.getString(3));
                t.setDate(rs.getString(4));
                t.setKeyWord(rs.getString(5));
                t.setLikeCount(rs.getInt(6));
                t.setName(rs.getString(7));
            }
            return t;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                rs = null;
                if (pstmt != null) {
                    pstmt.close();
                }
                pstmt = null;
                if (conn != null) {
                    conn.close();
                }
                conn = null;
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public List<Title> queryMyTitle(Title title) {
        List<Title> list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtil.getConnection();
            String sql = "select id,title,text,date,keyWord,likeCount,name from title where name=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,title.getName());
            rs = pstmt.executeQuery();
            Title t = null;
            while (rs.next()) {
                t = new Title();
                t.setId(rs.getInt(1));
                t.setTitle(rs.getString(2));
                t.setText(rs.getString(3));
                t.setDate(rs.getString(4));
                t.setKeyWord(rs.getString(5));
                t.setLikeCount(rs.getInt(6));
                t.setName(rs.getString(7));
                list.add(t);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                rs = null;
                if (pstmt != null) {
                    pstmt.close();
                }
                pstmt = null;
                if (conn != null) {
                    conn.close();
                }
                conn = null;
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public Title QuerybbsVoById(String name) {
        Title t = new Title();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtil.getConnection();
            String sql = "select id,title,text,date,keyWord,likeCount,name from title where name=?";
            pstmt.setString(1, t.getName());
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                t = new Title();
                t.setId(rs.getInt(1));
                t.setTitle(rs.getString(2));
                t.setText(rs.getString(3));
                t.setDate(rs.getString(4));
                t.setKeyWord(rs.getString(5));
                t.setLikeCount(rs.getInt(6));
                t.setName(rs.getString(7));
            }
            return t;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                rs = null;
                if (pstmt != null) {
                    pstmt.close();
                }
                pstmt = null;
                if (conn != null) {
                    conn.close();
                }
                conn = null;
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

    }

    @Override
    public Title queryByTitle(Title title) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtil.getConnection();
            String sql = "select id,title,text,date,keyWord,likeCount,name from title where title=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,title.getTitle());
            rs = pstmt.executeQuery();
            Title t = null;
            if(rs.next()){
                t = new Title();
                t.setId(rs.getInt(1));
                t.setTitle(rs.getString(2));
                t.setText(rs.getString(3));
                t.setDate(rs.getString(4));
                t.setKeyWord(rs.getString(5));
                t.setLikeCount(rs.getInt(6));
                t.setName(rs.getString(7));
            }
            return t;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                rs = null;
                if (pstmt != null) {
                    pstmt.close();
                }
                pstmt = null;
                if (conn != null) {
                    conn.close();
                }
                conn = null;
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public void update(Title title) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = JDBCUtil.getConnection();
            String sql = "update title set text=?,date=?,keyWord=? where title=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,title.getText());
            pstmt.setString(2,title.getDate());
            pstmt.setString(3,title.getKeyWord());
            pstmt.setString(4,title.getTitle());
            pstmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                pstmt = null;
                if (conn != null) {
                    conn.close();
                }
                conn = null;
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public void delete(Title title) {
        Connection conn =null;
        PreparedStatement pstmt = null;
        try {
            conn=JDBCUtil.getConnection();
            String sql = "delete from title where title=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,title.getTitle());
            pstmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                pstmt = null;
                if (conn != null) {
                    conn.close();
                }
                conn = null;
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}